﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace EmployeeCheckIn
{
    public class SerchDB
    {
        public DataTable Gviewdb(string indate,string lastdate,string name)
        {
                                             //,cast([CHECK_IN] as varchar(11)) as CHECK_IN
            string sqltext = @"SELECT EMPLOYEE.[EMP_ID]
                                            ,[EMP_CODE]
                                            ,[EMP_FIRSTNAME]
                                            ,[EMP_LASTNAME]
                                            ,[LATE_TIME]
                                            ,CHECK_IN
	                                        ,convert(varchar(5),[CHECK_IN],108) as timeonlyin
	                                        ,convert(varchar(5),[CHECK_OUT],108) as timeonlyout
                                            FROM EMPLOYEE inner join TBL_CHECK_IN
                                            on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID
                                            where [CHECK_IN] BETWEEN '" + indate + "' and '" + lastdate + "' AND ( EMPLOYEE.[EMP_FIRSTNAME] LIKE ('" + name + "%') OR EMPLOYEE.EMP_CODE LIKE ('" + name + "%')) ORDER BY CHECK_IN asc ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext,sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable DateOnly(string indate, string lastdate)
        {
            //,cast([CHECK_IN] as varchar(11)) as CHECK_IN
            string sqltext = @"SELECT EMPLOYEE.[EMP_ID]
                                            ,[EMP_CODE]
                                            ,[EMP_FIRSTNAME]
                                            ,[EMP_LASTNAME]
                                            ,[LATE_TIME]
                                            ,CHECK_IN
	                                        ,convert(varchar(5),[CHECK_IN],108) as 'timeonlyin'
	                                        ,convert(varchar(15),[CHECK_OUT],101) as[CHECK_OUT]
	                                        ,convert(varchar(5),[CHECK_OUT],108) as 'timeonlyout'
                                            FROM EMPLOYEE inner join TBL_CHECK_IN
                                            on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID
                                            where [CHECK_IN] BETWEEN '" + indate + "' and '" + lastdate + "' ORDER BY CHECK_IN ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable DateOnly(DateTime indate, DateTime betweendate)
        {
            string fristdate = indate.ToString("yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
            string lastdate  = betweendate.ToString("yyyy-MM-dd HH:mm:ss",new System.Globalization.CultureInfo("en-US"));
            //,cast([CHECK_IN] as varchar(11)) as CHECK_IN
            string sqltext = @"SELECT EMPLOYEE.[EMP_ID]
                                            ,[EMP_CODE]
                                            ,[EMP_FIRSTNAME]
                                            ,[EMP_LASTNAME]
                                            ,[LATE_TIME]
                                            ,CHECK_IN
	                                        ,convert(varchar(5),[CHECK_IN],108) as 'timeonlyin'
	                                        ,convert(varchar(15),[CHECK_OUT],101) as[CHECK_OUT]
	                                        ,convert(varchar(5),[CHECK_OUT],108) as 'timeonlyout'
                                            FROM EMPLOYEE inner join TBL_CHECK_IN
                                            on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID
                                            where CHECK_IN BETWEEN '" + fristdate + "' and '" + lastdate + "' ORDER BY CHECK_IN ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable NotDay(string name)
        {
            //,cast([CHECK_IN] as varchar(11)) as CHECK_IN
            string sqltext = @"SELECT EMPLOYEE.[EMP_ID]
                                            ,[EMP_CODE]
                                            ,[EMP_FIRSTNAME]
                                            ,[EMP_LASTNAME]
                                            ,[LATE_TIME]
                                            ,CHECK_IN
	                                        ,convert(varchar(5),[CHECK_IN],108) as 'timeonlyin'
	                                        ,convert(varchar(15),[CHECK_OUT],101) as[CHECK_OUT]
	                                        ,convert(varchar(5),[CHECK_OUT],108) as 'timeonlyout'
                                            FROM EMPLOYEE inner join TBL_CHECK_IN
                                            on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID
                                            where EMPLOYEE.[EMP_FIRSTNAME] LIKE ('" + name + "%') OR EMPLOYEE.EMP_CODE LIKE ('" + name + "%') ORDER BY CHECK_IN asc ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable SearchALL()
        {
            //,cast([CHECK_IN] as varchar(11)) as CHECK_IN
            string sqltext = @"SELECT EMPLOYEE.[EMP_ID]
                                            ,[EMP_CODE]
                                            ,[EMP_FIRSTNAME]
                                            ,[EMP_LASTNAME]
                                            ,[LATE_TIME]
                                            ,CHECK_IN
	                                        ,convert(varchar(5),[CHECK_IN],108) as 'timeonlyin'
	                                        ,convert(varchar(15),[CHECK_OUT],101) as[CHECK_OUT]
	                                        ,convert(varchar(5),[CHECK_OUT],108) as 'timeonlyout'
                                            FROM EMPLOYEE inner join TBL_CHECK_IN
                                            on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID
                                            ORDER BY [CHECK_IN]";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable timeofdate()
        {
            string sqltext = "select [TIME_OF_START],[LATE_MONEY],[TIME_OF_LATE],[DAY_OF_EDIT] from TBL_TIMEOFDAY ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable timestart()
        {
            string sqltext = "SELECT DAY_OF_EDIT,TIME_OF_START from TBL_TIMEOFDAY ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GviewSum(string indate, string lastdate, string name,int LateMoney,string LateTime)
        {
            string sqltext = "SELECT TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],[EMP_LASTNAME],Convert(datetime,MIN(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MinDay,CONVERT(DATETIME,MAX(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MaxDay ,SUM(CASE WHEN LATE_TIME = '0'THEN 1 ELSE 0 END) as Sum_Punctua,SUM(CASE WHEN LATE_TIME >= '1' THEN 1 ELSE 0 END) As Sum_LateDay,SUM(CASE WHEN LATE_TIME >= '0' THEN 1 ELSE 0 END) As Sum_Day,SUM(LATE_TIME)AS SUM_LATETIME,SUM(CASE WHEn LATE_TIME > '" + LateTime + "' THEN (LATE_TIME - '" + LateTime + "')*'" + LateMoney + "' ELSE 0 END ) As MoneyTotal FROM TBL_CHECK_IN INNER JOIN EMPLOYEE on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID WHERE [CHECK_IN] BETWEEN '" + indate + "' and '" + lastdate + "'GROUP BY TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],[EMP_LASTNAME] HAVING EMPLOYEE.EMP_CODE in ('" + name + "') OR EMPLOYEE.EMP_FIRSTNAME in ('" + name + "')";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GviewSumNotDay(string name, int LateMoney,string LateTime)
        {
            string sqltext = "SELECT TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],[EMP_LASTNAME],Convert(datetime,MIN(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MinDay,CONVERT(DATETIME,MAX(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MaxDay ,SUM(CASE WHEN LATE_TIME = '0'THEN 1 ELSE 0 END) as Sum_Punctua,SUM(CASE WHEN LATE_TIME >= '1' THEN 1 ELSE 0 END) As Sum_LateDay,SUM(CASE WHEN LATE_TIME >= '0' THEN 1 ELSE 0 END) As Sum_Day,SUM(LATE_TIME)AS SUM_LATETIME,SUM(CASE WHEn LATE_TIME > '" + LateTime + "' THEN (LATE_TIME - '" + LateTime + "')*'" + LateMoney + "' ELSE 0 END ) As MoneyTotal FROM TBL_CHECK_IN INNER JOIN EMPLOYEE on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID GROUP BY TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],[EMP_LASTNAME] HAVING EMPLOYEE.EMP_CODE in ('" + name + "') OR EMPLOYEE.EMP_FIRSTNAME in ('" + name + "')";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GviewSumDateOnly(string indate, string lastdate, int LateMoney,string LateTime)
        {
            string sqltext = "SELECT TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],[EMP_LASTNAME],Convert(datetime,MIN(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MinDay,CONVERT(DATETIME,MAX(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MaxDay ,SUM(CASE WHEN LATE_TIME = '0'THEN 1 ELSE 0 END) as Sum_Punctua,SUM(CASE WHEN LATE_TIME >= '1' THEN 1 ELSE 0 END) As Sum_LateDay,SUM(CASE WHEN LATE_TIME >= '0' THEN 1 ELSE 0 END) As Sum_Day,SUM(LATE_TIME)AS SUM_LATETIME,SUM(CASE WHEn LATE_TIME > '" + LateTime + "' THEN (LATE_TIME - '" + LateTime + "')*'" + LateMoney + "' ELSE 0 END ) As MoneyTotal FROM TBL_CHECK_IN INNER JOIN EMPLOYEE on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID WHERE [CHECK_IN] BETWEEN '" + indate + "' and '" + lastdate + "'GROUP BY TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],[EMP_LASTNAME] Order By EMP_ID";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GviewSumAllData(int LateMoney,string LateTime)
        {
            string sqltext = "SELECT TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],Convert(datetime,MIN(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MinDay,CONVERT(DATETIME,MAX(CONVERT(varchar(10),DATEADD(Year,0,CHECK_IN),101))) as MaxDay,[EMP_LASTNAME],SUM(CASE WHEN LATE_TIME = '0'THEN 1 ELSE 0 END) as Sum_Punctua,SUM(CASE WHEN LATE_TIME >= '1' THEN 1 ELSE 0 END) As Sum_LateDay,SUM(CASE WHEN LATE_TIME >= '0' THEN 1 ELSE 0 END) As Sum_Day,SUM(LATE_TIME)AS SUM_LATETIME,SUM(CASE WHEn LATE_TIME > '" + LateTime + "' THEN (LATE_TIME - '" + LateTime + "')*'" + LateMoney + "' ELSE 0 END ) As MoneyTotal FROM TBL_CHECK_IN INNER JOIN EMPLOYEE on	EMPLOYEE.EMP_ID = TBL_CHECK_IN.EMP_ID GROUP BY TBL_CHECK_IN.[EMP_ID],[EMP_CODE],[EMP_FIRSTNAME],[EMP_LASTNAME] Order By EMP_ID";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable Fromimport()
        {
            string sqltext = @"select	EMP_ID
		                                ,DateTime_IP 
		                                ,convert(varchar(5),CHECK_IN_IP,108)  as CHECKIN
		                                ,convert(varchar(5),CHECK_OUT_TP,108) as CHECKOUT
	                                    FROM  EMPLOYEE inner join TBL_IM_PORT
			                            on EMP_CODE = IDIP";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter dap = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];
        }

        public void ClearNull()
        {
            string sqltext = @"delete TBL_IM_PORT where IDIP  is null or DateTime_IP is null";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
        }

        public void ClearImport() 
        { 
            string sqltext = @"delete TBL_IM_PORT";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
        }
        
        
    }
}